<?php
require_once 'readmains_controller.php';
class UploadsController extends AppController {

	var $name = 'Uploads';
	var $components = array('FileUpload.FileUpload');
	var $uses = array('Upload','Sinhvien','Nganh','Lop','Lophp','Giangvien','Ketqua','Mon','Hknk','Bangdiem');
	
	function uploadsv()
	{
		if(!empty($this->data))
		{
			
			$er=$this->FileUpload->showErrors();
			if($this->FileUpload->success||empty($er))
			{
				$err = $this->__impTodb(WWW_ROOT.DS.'files'.DS.$this->data['Upload']['file']['name'],1);
			if($err)
				{// bua trc t keu sua may cai tren giong j ne
					// lam cai j ma ko sua co keu dau ma
					$this->Session->setFlash('Lỗi Import');
					$this->redirect(array('action'=>'uploadsv'),'Lỗi Import');
				}
				else
				{
					$this->Session->setFlash('Import Thành Công');
					$this->redirect(array('action'=>'uploadsv'));
				}
					
				@unlink(WWW_ROOT.DS.'files'.DS.$this->data['Upload']['file']['name']);
			}
		 else{
                $this->Session->setFlash($er);
            }
		}
	}

	function uploadgv()
	{
		if(!empty($this->data))
		{
			
			$er=$this->FileUpload->showErrors();
			if($this->FileUpload->success||empty($er))
			{
				$err = $this->__impTodb(WWW_ROOT.DS.'files'.DS.$this->data['Upload']['file']['name'],1);
				if($err)
					$this->redirect(array('action'=>'uploadgv'),'Lỗi Import');
				else
					$this->redirect(array('action'=>'uploadgv'),'Import Thành Công');
					
				@unlink(WWW_ROOT.DS.'files'.DS.$this->data['Upload']['file']['name']);
			}
		 else{
                $this->Session->setFlash($er);
            }
		}
	}
	
	function uploadlhp()
	{
		if(!empty($this->data))
		{
			
			$er=$this->FileUpload->showErrors();
			if($this->FileUpload->success||empty($er))
			{
				$hknk = $this->Hknk->find('list',array('conditions'=>array('nienkhoa'=>$this->data['Upload']['khoahoc'],'hocky'=>$this->data['Upload']['hocky'])));
				if(empty($hknk))
				{
					$record['Hknk']['hocky'] = $this->data['Upload']['hocky'];
					$record['Hknk']['khoahoc'] = $this->data['Upload']['khoahoc'];
					$this->Hknk->save($record);
					$hknk = $this->Hknk->find('list',array('conditions'=>array('nienkhoa'=>$this->data['Upload']['khoahoc'],'hocky'=>$this->data['Upload']['hocky'])));
				}
				$err = $this->__impTodb(WWW_ROOT.DS.'files'.DS.$this->data['Upload']['file']['name'],2,key($hknk));
				if($err)
				{// bua trc t keu sua may cai tren giong j ne
					// lam cai j ma ko sua co keu dau ma
					$this->Session->setFlash('Lỗi Import');
					$this->redirect(array('action'=>'uploadlhp'),'Lỗi Import');
				}
				else
				{
					$this->Session->setFlash('Import Thành Công');
					$this->redirect(array('action'=>'uploadlhp'));
				}
					
				@unlink(WWW_ROOT.DS.'files'.DS.$this->data['Upload']['file']['name']);
			}
		 else{
                $this->Session->setFlash($er);
            }
		}
		$khoahocs = $this->Hknk->find('list',array('fields'=>array('nienkhoa','nienkhoa'),'group'=>array('nienkhoa'),'order'=>array('nienkhoa DESC')));
		$hockys = $this->Hknk->find('list',array('fields'=>array('hocky','hocky'),'group'=>array('hocky'),'order'=>array('hocky DESC')));
		$this->set(compact('khoahocs','hockys'));
	}

	function uploaddiemsv()
	{
		if(!empty($this->data))
		{
			
			$er=$this->FileUpload->showErrors();
			if($this->FileUpload->success||empty($er))
			{
				$err = $this->__impTodb(WWW_ROOT.DS.'files'.DS.$this->data['Upload']['file']['name'],3);
				if($err) // sua may cai nay giong o tren
				{
					$this->Session->setFlash('Lỗi Import');
					$this->redirect(array('action'=>'uploaddiemsv'),'Lỗi Import');
				}
				else
				{
					$this->Session->setFlash('Import Thành Công');
					$this->redirect(array('action'=>'uploaddiemsv'));
				}
					
				@unlink(WWW_ROOT.DS.'files'.DS.$this->data['Upload']['file']['name']);
			}
		 else{
                $this->Session->setFlash($er);
            }
		}
	}
	
/**
     * __impTodb($newfilename, $client_filename)
     * 
     * HÃ m nÃ y Ä‘á»�c dá»¯ liá»‡u tá»« 1 file excel (read content of excel file)
     * LÆ°u trá»¯ dá»¯ liá»‡u Ä‘á»�c Ä‘Æ°á»£c vÃ o csdl, lÆ°u theo tá»«ng dÃ²ng trÃªn excel (save to DB, save each row)
     *  
     * @param mixed $newfilename Ä‘Æ°á»�ng dáº«n Ä‘áº¿n file excel trÃªn server (the patch of excel on server)
     * @param mixed $client_filename tÃªn cá»§a file excel lÃºc táº£i lÃªn (old filename before upload)
     *          
     * @return number (==0 if succeed, otherwise there are 7 error cases)
     * 
     */ 
   function __impTodb($filename,$loai=1,$hknk=1)
   {
   		$err=0;
   		set_time_limit(300);
        //doc du lieu tu tap tin tren server
        $data = new Spreadsheet_Excel_Reader($filename,true,'UTF-8');
        $dulieus = $data->dump(true,true,0);
        //debug($dulieus);
        if($loai==1)
        {
	        $nganhs = array();
	        $lops = array();
	        $this->Nganh->displayField='tennganh';
	        $nganhdacos = $this->Nganh->find('list');
	        
	        $this->Lop->displayField='malop';
	        $lopdacos = $this->Lop->find('list');
	   		foreach($nganhdacos as $key => $nganhdaco)
	        {
	        	$nganhdacos[$key] = strtolower($nganhdaco);
	        }
	        foreach($dulieus as $dulieu)
	        {
	        	if(!in_array(strtolower($dulieu[2]),$nganhdacos))
	        		if(!in_array(strtolower($dulieu[2]), $nganhs) && !empty($dulieu[2]))
	        		{
	        			/*$temp = array();
	        			$temp['khoa_id'] = 1;
	        			$temp['tennganh'] = strtolower($dulieu[2]);*/
	        			
	        			$nganhs[] =strtolower($dulieu[2]);
	        		}
	        			
	        	if(!in_array(strtoupper($dulieu[4]),$lopdacos))
	        		if(!in_array(strtoupper($dulieu[4]).'_'.$dulieu[5].'_'.$dulieu[2], $lops)&& !empty($dulieu[4]))
	        		{
	        			/*$temp = array();
	        			$temp['malop'] = strtolower($dulieu[4]);
	        			$temp['tenLop'] = $dulieu[5];*/
	        			$lops[] = strtoupper($dulieu[4]).'_'.$dulieu[5].'_'.$dulieu[2];
	        		}
	       }
	       /// them nganh va khoa hoc 
	       $this->Nganh->create();
	       foreach ($nganhs as $nganh)
	       {
	       		$recordng['Nganh']['tennganh']= $nganh;
	       		$ngnh = $nganh;
	       		if(!$this->Nganh->saveAll($recordng))
	       			$err=1;
	       }
	       $nganhdacos = $this->Nganh->find('list',array('fields'=>array('tennganh','id')));
	       $this->Lop->create();
	       foreach ($lops as $lop)
	       {
	       		$tmp = split('_',$lop);
	       		$recordlop['Lop']['nganh_id']= $nganhdacos[$tmp[2]];
	       		$recordlop['Lop']['malop']= $tmp[0];
	       		$recordlop['Lop']['tenLop']= $tmp[1];
	       		if(!$this->Lop->saveAll($recordlop))
	       			$err=1;
	       }
	        $lopdangcos = $this->Lop->find('list',array('fields'=>array('malop','id')));
	        
	        foreach ( $dulieus as $sinhvien)
	        {
	        	if(isset($lopdangcos[$sinhvien[4]]))
	        	{
	        		$dg = $this->Sinhvien->find('list',array('conditions'=>array('mssv'=>$sinhvien[6])));
	        		if(empty($dg))
	        		{
			        	$recorddg['Sinhvien']['mssv']=$sinhvien[6];
			        	//$recorddg['Sinhvien']['user_id']=0;
			        	$recorddg['Sinhvien']['tenSV']=$sinhvien[7];
			        	$recorddg['Sinhvien']['gioiTinhSV']=$sinhvien[12];
			        	$recorddg['Sinhvien']['ngaySinhSV']=date('Y-m-d',strtotime($sinhvien[8]));
			        	$recorddg['Sinhvien']['lop_id']=$lopdangcos[$sinhvien[4]];
			        	if(!$this->Sinhvien->saveAll($recorddg))
			        	{
			        		$err=1;
			        	}
	        		}
	        		//debug($recorddg);
	        	}
	        }
	        return $err;
        }
        elseif($loai==2)
        {
        	$lophps =array();
        	$monhps = array();
        	foreach ($dulieus as $dulieu)
        	{
        		if($dulieu[1]!='STT' && !in_array($dulieu[9],$lophps))
        		{
        			$lophps[] = $dulieu[9];
        			$monhps[$dulieu[9]]=$dulieu[2].'_'.$dulieu[3];
        		}
        	}
        	
        	$lophpdaco = $this->Lophp->find('list',array('fields'=>array('id','malophp')));
        	//$monhps  = $this->Mon->find('list',array('fields'=>array('tenMon','id')));
        	foreach ($lophps as $lophp)
        	{
        		$temp = split('_',$monhps[$lophp]);
        		$mh = $this->Mon->find('list',array('conditions'=>array('mamom'=>$temp[0])));
        		$record['Lophp']['malophp']=$lophp;
        		$record['Lophp']['mon_id']=key($mh);
        		$record['Lophp']['kyhieu']=$temp[1];
        		$record['Lophp']['hknk_id']=$hknk;
        		if(!$this->Lophp->saveAll($record))
        		{
        			$err = 1;
        		}
        	}
        }
        elseif($loai ==3)
        {
        	$err = 0;
        	//debug($dulieus);
        	$nienkhoa = $dulieus[5][2];
        	$hocky =  $dulieus[5][4];
        	$monhoc =  $dulieus[4][2];
        	$kyhieu =  $dulieus[4][4];
        	/*if($kyhieu >=1 && $kyhieu < 10)
        	{
        		$kyhieu = '0'.$kyhieu;
        	}*/
        	$hknk_id = $this->Hknk->find('list',array('conditions'=>array('nienkhoa'=>$nienkhoa,'hocky'=>$hocky)));
        	$lophp = $this->Lophp->find('list',array('recursive'=>0,'conditions'=>array('hknk_id'=>key($hknk_id),'Mon.mamom'=>$monhoc,'kyhieu'=>$kyhieu)));
        	$len  = count($dulieus);
        	$mssv1 = $dulieus[8][2];
        	for($i = 9; $i<$len; $i++)
        	{
        		if($dulieus[$i][5]==$mssv1)
        		{
        			$stt = $i;
        			break;
        		}
        	}
        	$diems = $this->Bangdiem->find('list',array('fields'=>array('diemchu','id')));
        	//debug($diems);
        	for($i = $stt; $i<$len; $i++)
        	{
        		$sinhvien = $this->Sinhvien->find('list',array('conditions'=>array('mssv'=>$dulieus[$i][5]),'fields'=>array('id','id')));
        		//debug($sinhvien);
        		if(!empty($sinhvien))
        		{
        			//debug($record);
	        		$record['Ketqua']['lophp_id'] =key($lophp);
	        		$record['Ketqua']['tichLuy'] = $diems[$dulieus[$i][8]];
	        		$record['Ketqua']['sinhvien_id'] = key($sinhvien);
	        		$kq = $this->Ketqua->find('list',array('conditions'=>array('lophp_id'=>key($lophp),'sinhvien_id'=>key($sinhvien))));
	        		if(!empty($kq))
	        		{
	        			$record['Ketqua']['id'] =key($kq);
	        		}
	        		if(!$this->Ketqua->saveAll($record))
	        				$err = 1;
        		}
        	}
        }
   		/*elseif($loai==4)
        {
        	$gvs =array();
        	//$monhps = array();
        	//debug($gvs);
        	foreach ($dulieus as $giangvien)
        	{
        		//$mh = $this->Mon->find('list',array('conditions'=>array('mamom'=>$temp[0])));
        		//$record['Lophp']['malophp']=$lophp;
        		//$record['Lophp']['mon_id']=key($mh);
        		//$record['Lophp']['kyhieu']=$temp[1];
        		//$record['Lophp']['hknk_id']=$hknk;
        		//if(!$this->Lophp->saveAll($record))
        		{
        			//$err = 1;
        		}
        		
        	}
        }
*/        return $err;
   }
   
}
